package cn.edu.seu.cose.qol.model.dao.database.account;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.LinkedList;

import cn.edu.seu.cose.qol.model.dao.database.IncompleteInputInformation;
import cn.edu.seu.cose.qol.model.dao.database.QolSchemaConnectionFactory;
import cn.edu.seu.cose.qol.model.po.account.AdminAccount;


public class AdminAccountDataAccess {

	private static AdminAccountDataAccess _instance;
	
	private static final String COLUMN_ADMIN_ACCOUNT_ID = "admin_account_id";
	
	private static final String COLUMN_USERNAME = "username";
	
	private static final String COLUMN_PASSWORD = "password";

	private static final String STATEMENT_RETRIEVE_ADMIN_ACCOUNT_BY_ID =
			"SELECT * FROM qol_admin_account WHERE admin_account_id=?;";

	private static final String
			STATEMENT_RETRIEVE_ADMIN_ACCOUNT_BY_USERNAME =
				"SELECT * FROM qol_admin_account WHERE username=?;";
	
	private static final String STATEMENT_RETRIEVE_ADMIN_ACCOUNTS =
			"SELECT * FROM qol_admin_account;";
	
	private static final String STATEMENT_RETRIEVE_ADMIN_ACCOUNTS_BY_LIMIT =
			"SELECT * FROM qol_admin_account LIMIT ?, ?;";
	
	private static final String STATEMENT_AUTHENTICATE_ADMIN_ACCOUNT =
			"SELECT * FROM qol_admin_account " +
			"WHERE username=? AND password=PASSWORD(?);";
	
	private static final String STATEMENT_CREATE_ADMIN_ACCOUNT =
			"INSERT INTO qol_admin_account (username, password) " +
			"VALUES (?, PASSWORD(?));";
	
	private static final String STATEMENT_DELETE_ADMIN_ACCOUNT_BY_ID =
			"DELETE FROM qol_admin_account WHERE admin_account_id=?;";
	
	private static final String STATEMENT_DELETE_ADMIN_ACCOUNT_BY_USERNAME =
			"DELETE FROM qol_admin_account WHERE username=?;";
	
	private static final String STATEMENT_UPDATE_ADMIN_ACCOUNT =
			"UPDATE qol_admin_account " +
			"SET username=?, password=PASSWORD(?) WHERE admin_account_id=?;";
	
	private static final String STATEMENT_UPDATE_ADMIN_ACCOUNT_USERNAME =
			"UPDATE qol_admin_account SET username=? WHERE admin_account_id=?;";
	
	private static final String STATEMENT_UPDATE_ADMIN_ACCOUNT_PASSWORD =
			"UPDATE qol_admin_account SET password=PASSWORD(?) " +
			"WHERE admin_account_id=?;";
	
	private QolSchemaConnectionFactory _factory;
	
	public static AdminAccountDataAccess getInstance() {
		if (_instance == null) {
			_instance = new AdminAccountDataAccess();
		}
		
		return _instance;
	}
	
	private static AdminAccount getAdminAccountByResultSet(ResultSet rs)
			throws SQLException {
		long adminAccountId = rs.getLong(COLUMN_ADMIN_ACCOUNT_ID);
		String username = rs.getString(COLUMN_USERNAME);
		String password = rs.getString(COLUMN_PASSWORD);
		AdminAccount adminAccount = new AdminAccount();
		adminAccount.setAdminAccountId(adminAccountId);
		adminAccount.setUsername(username);
		adminAccount.setPassword(password);
		
		return adminAccount;
	}
	
	protected AdminAccountDataAccess() {
		_factory = QolSchemaConnectionFactory.getInstance();
	}

	public AdminAccount getAdminAccountById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
							STATEMENT_RETRIEVE_ADMIN_ACCOUNT_BY_ID);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getAdminAccountByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	public AdminAccount getAdminAccountByUsername(String name)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_RETRIEVE_ADMIN_ACCOUNT_BY_USERNAME);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getAdminAccountByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	public Collection<AdminAccount> getAdminAccounts() throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_ADMIN_ACCOUNTS);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<AdminAccount> adminAccounts =
					new LinkedList<AdminAccount>();
			while (rs.next()) {
				adminAccounts.add(getAdminAccountByResultSet(rs));
			}
			return adminAccounts;
		} finally {
			connection.close();
		}
	}
	
	public Collection<AdminAccount> getAdminAccounts(long offset, long row)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_ADMIN_ACCOUNTS_BY_LIMIT);
			ps.setLong(1, offset);
			ps.setLong(2, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<AdminAccount> adminAccounts =
					new LinkedList<AdminAccount>();
			while (rs.next()) {
				adminAccounts.add(getAdminAccountByResultSet(rs));
			}
			return adminAccounts;
		} finally {
			connection.close();
		}
	}
	
	public boolean authenticateAdminAccount(AdminAccount adminAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_AUTHENTICATE_ADMIN_ACCOUNT);
			String username = adminAccount.getUsername();
			String password = adminAccount.getPassword();
			ps.setString(1, username);
			ps.setString(2, password);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return false;
			}
			
			return true;
		} finally {
			connection.close();
		}
	}
	
	public long createNewAdminAccount(AdminAccount adminAccount)
			throws SQLException {
		if ((adminAccount.getUsername() == null)
				|| (adminAccount.getPassword() == null)) {
			throw new IncompleteInputInformation();
		}
		
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_ADMIN_ACCOUNT,
					Statement.RETURN_GENERATED_KEYS);
			String username = adminAccount.getUsername();
			String password = adminAccount.getPassword();
			ps.setString(1, username);
			ps.setString(2, password);
			ps.executeUpdate();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (!rs.next()) {
				return -1;
			}

			return rs.getLong(1);
		} finally {
			connection.close();
		}
	}
	
	public void deleteAdminAccountById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
							STATEMENT_DELETE_ADMIN_ACCOUNT_BY_ID);
			ps.setLong(1, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteAdminAccountByUsername(String username)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_DELETE_ADMIN_ACCOUNT_BY_USERNAME);
			ps.setString(1, username);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void updateAdminAccount(AdminAccount adminAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_UPDATE_ADMIN_ACCOUNT);
			long id = adminAccount.getAdminAccountId();
			String username = adminAccount.getUsername();
			String password = adminAccount.getPassword();
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setLong(3, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void updateAdminAccountUsername(AdminAccount adminAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_UPDATE_ADMIN_ACCOUNT_USERNAME);
			long id = adminAccount.getAdminAccountId();
			String username = adminAccount.getUsername();
			ps.setString(1, username);
			ps.setLong(2, id);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}
	
	public void updateAdminAccountPassword(AdminAccount adminAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_UPDATE_ADMIN_ACCOUNT_PASSWORD);
			long id = adminAccount.getAdminAccountId();
			String password = adminAccount.getPassword();
			ps.setString(1, password);
			ps.setLong(2, id);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}

}
